﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DataLayer
{
    public class dlmyview
    {

        public DataSet getuserfulldetail(int userid)
        {
            string connStr = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
            string querystring = @"SELECT        Roles.RolesId, Roles.Role, Users.UserID, Users.RoleId, Users.Login, Users.Passwoord, Users.Name, Users.FirstName
FROM            Roles INNER JOIN
                         Users ON Roles.RolesId = Users.RoleId
WHERE        (Users.UserID = '"+userid+"')";

            SqlConnection connection = new SqlConnection(connStr);
            SqlCommand querylogin = new SqlCommand(querystring, connection);

            SqlDataAdapter myadapter = new SqlDataAdapter(querylogin);
            DataSet mydataset = new DataSet();

            myadapter.Fill(mydataset);

            return mydataset;

        }


        // Remake the SQL Query with super mega inner join (keeping inner peace, NOT)
        public DataSet getusertasks(int userid)
        {
            string connStr = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;

            string querystring = @"SELECT        Tasks.Titel, Tasks.Status, Sprints.Titel AS SprintTitel, Sprints.EstimatedEndDate AS SprintDueDate, Teams.Name AS TeamName, 
                         UserStory.Titel AS UserStoryTitel, Tasks.TaskId
FROM            Tasks INNER JOIN
                         UserStory ON Tasks.UserStoryId = UserStory.UserStoryId INNER JOIN
                         Sprints ON UserStory.SprintID = Sprints.SprintId INNER JOIN
                         Product ON Sprints.ProductID = Product.ProductId INNER JOIN
                         Teams ON Sprints.TeamId = Teams.TeamID INNER JOIN
                         Users ON Tasks.UserId = Users.UserID
WHERE        (Users.UserID = '" + userid + "')";


            SqlConnection connection = new SqlConnection(connStr);
            SqlCommand querylogin = new SqlCommand(querystring, connection);

            SqlDataAdapter myadapter = new SqlDataAdapter(querylogin);
            DataSet mydataset = new DataSet();

            myadapter.Fill(mydataset);

            return mydataset;

        }


        public DataSet getuserteams(int userid)
        {
            string connStr = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;

            string querystring = @"SELECT Teams.Name
FROM     Users INNER JOIN
                  TeamUser ON Users.UserID = TeamUser.UserId INNER JOIN
                  Teams ON TeamUser.TeamId = Teams.TeamID
WHERE  (Users.UserID = "+userid+")";

            SqlConnection connection = new SqlConnection(connStr);
            SqlCommand querylogin = new SqlCommand(querystring, connection);

            SqlDataAdapter myadapter = new SqlDataAdapter(querylogin);
            DataSet mydataset = new DataSet();

            myadapter.Fill(mydataset);

            return mydataset;

        }
    }
}
